﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;
namespace KPIS.GERP.GBMS.BLL
{
    public class ComboBLL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable ListSubActKPI(string year)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_year", year);
                string sql = " SELECT bgm_strat_list_seq ,bgm_strat_list_name"
                             + " FROM bgm_strategy_data_list"
                             + " WHERE bgm_strat_type_seq='11' "
                             + " and year_start= @bgm_year";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListStrategy(string Year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@year", Year);

                string sql = "select dl.bgm_strat_list_seq as value, dl.bgm_strat_list_name as text"
                             + " from bgm_strategy_data_mapping mp"
                             + " inner join bgm_strategy_data_list dl on mp.bgm_strat_list_seq = dl.bgm_strat_list_seq"
                             + " where mp.bgm_year = @year"
                             + " and mp.record_status = @RecordStatus"
                             +
                             " and mp.bgm_strat_data_map_seq not in ( select upper_bgm_strat_data_map_seq from bgm_strategy_data_mapping where record_status = @RecordStatus and bgm_year = @year and upper_bgm_strat_data_map_seq is not null )"
                             + " order by dl.bgm_strat_list_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListStrategy(string Year, string ApprovalHeader)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@year", Year);
                dbManager.AddParameters(2, "@ApprovalHeader", ApprovalHeader);

                string sql = " select DISTINCT dl.bgm_strat_list_seq as value, dl.bgm_strat_list_name as text"
                             + " from bgm_strategy_data_mapping mp"
                             + " inner join bgm_strategy_data_list dl on mp.bgm_strat_list_seq = dl.bgm_strat_list_seq"
                             +
                             " inner join bgm_approval a ON dl.bgm_strat_list_seq = a.bgm_strat_list_seq and a.bgm_app_header_seq = @ApprovalHeader"
                             + " where mp.bgm_year = @year"
                             + " and mp.record_status = @RecordStatus"
                             +
                             " and mp.bgm_strat_data_map_seq not in ( select upper_bgm_strat_data_map_seq from bgm_strategy_data_mapping where record_status = @RecordStatus and bgm_year = @year and upper_bgm_strat_data_map_seq is not null )"
                             + " order by dl.bgm_strat_list_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable TreeStrategy(string year)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                string sql = " select mp.bgm_strat_data_map_seq as id, case when st.isroot = 1 then null else mp.upper_bgm_strat_data_map_seq end as parent, d.bgm_strat_list_seq as value, d.bgm_strat_list_name as text, (select case when count(*) > 0 then 'parent' else 'child' end from bgm_strategy_data_mapping s where s.upper_bgm_strat_data_map_seq = mp.bgm_strat_data_map_seq) as catagory"
                             + " from bgm_strategy_data_list d"
                             + " inner join bgm_mas_strategy_type st on d.bgm_strat_type_seq = st.bgm_strat_type_seq"
                             +
                             " inner join bgm_strategy_data_mapping mp on d.bgm_strat_list_seq = mp.bgm_strat_list_seq"
                             + " inner join (select mp.full_path, d1.bgm_strat_type_seq"
                             + " from  bgm_mas_strategy_type st"
                             + " left join bgm_strategy_data_list d1 on d1.bgm_strat_type_seq = st.bgm_strat_type_seq"
                             +
                             " left join bgm_strategy_data_mapping mp on d1.bgm_strat_list_seq = mp.bgm_strat_list_seq"
                             + " where st.isroot = 1"
                             + " ) t on mp.full_path like concat(t.full_path, '%')"
                             + " where mp.bgm_year = " + year
                             + " and mp.record_status = @RecordStatus";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListExhibition(string seq)
        {
            try
            {
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select bgm_option_seq as value, bgm_option_name as text"
                             + " from bgm_mas_option"
                             + " where bgm_option_type_seq = @seq and record_status = @RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectType()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select prj_type_seq as value, prj_type_name as text"
                             + " from bgm_mas_project_type"
                             + " where record_status=@RecordStatus"
                             + " order by prj_type_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectTypeWithNewProjectComeFirst()
        {
            // normal project types with posible new project didn't come first
            DataTable projectTypesTemp = ListProjectType();

            // reorder project type data to make new project come first 
            // copy column
            DataTable projectTypesReOrder = projectTypesTemp.Copy();
            projectTypesReOrder.Rows.Clear();

            // get "โครงการใหม่" row
            foreach (DataRow dataRow in projectTypesTemp.Rows)
            {
                if ((dataRow[1] as string).Contains("โครงการใหม่"))
                {
                    DataRow dataRowToAdd = projectTypesReOrder.NewRow();
                    dataRowToAdd[0] = dataRow[0];
                    dataRowToAdd[1] = dataRow[1];
                    projectTypesReOrder.Rows.Add(dataRowToAdd);
                    break;
                }
            }

            // get other project that not "โครงการใหม่" row
            foreach (DataRow dataRow in projectTypesTemp.Rows)
            {
                if (!(dataRow[1] as string).Contains("โครงการใหม่"))
                {
                    DataRow dataRowToAdd = projectTypesReOrder.NewRow();
                    dataRowToAdd[0] = dataRow[0];
                    dataRowToAdd[1] = dataRow[1];
                    projectTypesReOrder.Rows.Add(dataRowToAdd);
                }
            }

            return projectTypesReOrder;
        }

        public DataTable ListProject(string bgm_year, string job_site_seq, string bgm_strat_list_seq)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@bgm_strat_list_seq", bgm_strat_list_seq);
                string sql = "select bgm_proj_seq, bgm_proj_name"
                             + " from bgm_project"
                             + " where record_status=@RecordStatus"
                             + (!string.IsNullOrEmpty(bgm_year) ? " and bgm_year = @bgm_year" : "")
                             + (!string.IsNullOrEmpty(job_site_seq) ? " and job_site_seq = @job_site_seq" : "")
                             +
                             (!string.IsNullOrEmpty(bgm_strat_list_seq)
                                  ? " and bgm_strat_list_seq = @bgm_strat_list_seq"
                                  : "")
                             + " order by bgm_proj_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListScope()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select bgm_scope_seq as value, bgm_scope_name as text"
                             + " from bgm_mas_scopes"
                             + " where record_status=@RecordStatus"
                             + " order by bgm_scope_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListKpiType()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select bgm_kpi_type_seq as value, bgm_kpi_type_name as text"
                             + " from bgm_mas_kpi_type"
                             + " where record_status =@RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListExpenseCombo(string bgm_exp_grp_seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                string sql = " select e.bgm_exp_seq as value"
                             + ", concat(e.bgm_exp_name, '/', e1.bgm_exp_name, '/', e2.bgm_exp_name) as text"
                             +
                             ", eg.bgm_exp_grp_seq, concat(eg.bgm_exp_grp_code, '-' , eg.bgm_exp_grp_name) as bgm_exp_grp_name"
                             + " from bgm_mas_expense e"
                             + " left join bgm_mas_expense e1 on e.upper_bgm_exp_seq = e1.bgm_exp_seq"
                             + " left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"
                             +
                             " where e.bgm_exp_seq not in (select upper_bgm_exp_seq from bgm_mas_expense where record_status = @RecordStatus and upper_bgm_exp_seq is not null)"
                             + " and e.record_status = @RecordStatus"
                             + " order by e.bgm_exp_name, e1.bgm_exp_name, e2.bgm_exp_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListExpenseGrid()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select e.bgm_exp_seq as value, e.bgm_exp_name as text"
                             +
                             ", eg.bgm_exp_grp_seq, concat(eg.bgm_exp_grp_code, '-' , eg.bgm_exp_grp_name) as bgm_exp_grp_name"
                             + " from bgm_mas_expense e"
                             + " inner join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                             +
                             " where e.bgm_exp_seq not in ( select upper_bgm_exp_seq from bgm_mas_expense where record_status = @RecordStatus and upper_bgm_exp_seq is not null)"
                             + " and e.record_status = @RecordStatus"
                             + " order by eg.bgm_exp_grp_code, e.bgm_exp_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListExpenseGroup()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select bgm_exp_grp_seq as value, bgm_exp_grp_name as text , upper_bgm_exp_grp_seq as parent, case when (select count(*) from bgm_mas_expense_group s where s.upper_bgm_exp_grp_seq = m.bgm_exp_grp_seq and s.record_status = 'U') > 0 then 'parent' else 'child' end as category"
                             + " from bgm_mas_expense_group m"
                             + " where record_status = @RecordStatus";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListJobsite()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select job_site_seq as value, job_site_name as text"
                             + " from hrm_mp_job_sites"
                             + " where record_status=@RecordStatus"
                             + " and job_site_type_seq = 2"
                             + " and job_site_level_seq = 3"
                             //+ " and job_site_gfmis is not Null"
                             + " order by job_site_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListAllJobsite()
        {
            try
            { 
                string sql = " select job_site_seq as value, job_site_name as text"
                             + " ,job_site_type_seq, record_status "
                             + " from hrm_mp_job_sites"
                    //         + " where record_status=@RecordStatus" 
                    //+ " and job_site_gfmis is not Null"
                             + " order by record_status, job_site_name, job_site_type_seq";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable JobsiteInfo(int job_site_seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                string sql = "select job_site_seq, job_site_name"
                             + " from hrm_mp_job_sites"
                             + " where job_site_seq = @job_site_seq";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable TreeJobsiteTree()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select job_site_seq as id, job_site_seq as value, job_site_name as text, upper_job_site_seq as parent"
                             + " from hrm_mp_job_sites"
                             +
                             " where record_status=@RecordStatus and job_site_type_seq=2 and job_site_gfmis is not Null";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListSourceCombo()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select bgm_source_seq as value, bgm_source_name as text"
                             + " from bgm_mas_source m"
                             + " where record_status = @RecordStatus"
                             + " and bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status = @RecordStatus and upper_bgm_source_seq is not null)";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListSourceTree()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select bgm_source_seq as value, bgm_source_name as text, upper_bgm_source_seq as parent"
                             +
                             " , case when (select count(*) from bgm_mas_source s where s.upper_bgm_source_seq = m.bgm_source_seq) > 0 then 'parent' else 'child' end as category"
                             + " from bgm_mas_source m"
                             + " where  record_status = @RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListExpenseTree()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select concat('expensegroup-', cast(bgm_exp_grp_seq as char)) as id, bgm_exp_grp_name as name"
                             + " , concat('expensegroup-', cast(upper_bgm_exp_grp_seq as char)) as parent"
                             + " , bgm_exp_grp_seq as value, bgm_exp_grp_name as text"
                             + " , 'parent' as category"
                             + " from bgm_mas_expense_group eg"
                             + " where  record_status = @RecordStatus"
                             + " union all"
                             + " select concat('expense-', cast(bgm_exp_seq as char)) as id, bgm_exp_name as name"
                             +
                             " , coalesce(concat('expense-', cast(upper_bgm_exp_seq as char)), concat('expensegroup-', cast(bgm_exp_grp_seq as char))) as parent"
                             + " , bgm_exp_seq as value, bgm_exp_name as text"
                             +
                             " , case when (select count(*) from bgm_mas_expense s where s.upper_bgm_exp_seq = e.bgm_exp_seq) > 0 then 'parent' else 'child' end as category"
                             + " from bgm_mas_expense e"
                             + " where record_status = 'U'";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListResourcTree(string parent_id)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);
                string sql = "SELECT main.* FROM "
                             + "(SELECT CAST(ICM_RES_TYPE_SEQ AS CHAR) AS ID, NULL AS PARENT_ID"
                             + ", CONCAT(ICM_RES_TYPE_CODE, ' - ' , ICM_RES_TYPE_NAME) AS NAME"
                             + ", ICM_RES_TYPE_DESCR AS DESCR"
                             +
                             ", (select count(icm_res_type_seq) from icm_mas_resource_group where icm_res_type_seq = ICM_MAS_RESOURCE_TYPE.icm_res_type_seq) AS CHILDREN_COUNT"
                             + ", '../Image/Tree/folder-1.gif' AS IMAGE_URL"
                             + ", 'TYPE' AS Category"
                             + " FROM ICM_MAS_RESOURCE_TYPE"
                             + " WHERE RECORD_STATUS not like @delete_status"
                             + " UNION ALL"
                             + " SELECT  CAST(CONCAT(ICM_RES_TYPE_SEQ, '-', ICM_RES_GRP_SEQ) AS CHAR) AS ID"
                             + ", CAST(ICM_RES_TYPE_SEQ AS CHAR) AS PARENT_ID"
                             + ", CONCAT(ICM_RES_GRP_CODE, ' - ' , ICM_RES_GRP_NAME) AS NAME"
                             + ", ICM_RES_GRP_DESCR AS DESCR"
                             +
                             ", (select count(icm_res_grp_seq) from icm_mas_resource_sub_group where icm_res_grp_seq = ICM_MAS_RESOURCE_GROUP.icm_res_grp_seq) AS CHILDREN_COUNT"
                             + ", '../Image/Tree/folder-2.gif' AS IMAGE_URL"
                             + ", 'GROUP' AS Category"
                             + " FROM ICM_MAS_RESOURCE_GROUP"
                             + " WHERE RECORD_STATUS not like @delete_status"
                             + " UNION ALL"
                             +
                             " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR)  AS ID"
                             + ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR) AS PARENT_ID"
                             + ", CONCAT(sg.ICM_RES_SUB_GRP_CODE, ' - ' , sg.ICM_RES_SUB_GRP_NAME) AS NAME"
                             + ", sg.ICM_RES_SUB_GRP_DESCR AS DESCR"
                             +
                             ", (select count(icm_res_item_seq) from icm_mas_resource_item where icm_res_sub_grp_seq = sg.icm_res_sub_grp_seq) AS CHILDREN_COUNT"
                             + ", '../Image/Tree/folder-3.gif' AS IMAGE_URL"
                             + ", 'SUBGROUP' AS Category"
                             + " FROM ICM_MAS_RESOURCE_SUB_GROUP sg"
                             + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON sg.ICM_RES_GRP_SEQ = g.ICM_RES_GRP_SEQ"
                             + " WHERE sg.RECORD_STATUS not like @delete_status"
                             + " UNION ALL"
                             +
                             " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR)  AS ID"
                             +
                             ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR) AS PARENT_ID"
                             + ", CONCAT(i.ICM_RES_ITEM_CODE, ' - ' , i.ICM_RES_ITEM_NAME) AS NAME"
                             + ", u.ICM_UNIT_NAME AS DESCR"
                             + ", 0 AS CHILDREN_COUNT"
                             + ", '../Image/Tree/folder-4.gif' AS IMAGE_URL"
                             + ", 'ITEM' AS Category"
                             + " FROM ICM_MAS_RESOURCE_ITEM i"
                             +
                             " INNER JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                             + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                             + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                             + " WHERE i.RECORD_STATUS not like @delete_status"
                             + ") main"
                             + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
                             + " ORDER BY NAME";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetvalueResourceGroup(string icm_res_grp_name)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@icm_res_grp_name", icm_res_grp_name);

                string sql = " select icm_res_grp_seq as VALUES "
                             + " from icm_mas_resource_group "
                             + " where icm_res_grp_name LIKE '%" + icm_res_grp_name + "%' ";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                string Value = "0";
                if (dt.Rows.Count > 0)
                {
                    Value = dt.Rows[0][0].ToString();
                }
                return Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public string GetResourceGrp(string icm_res_sub_grp_seq)
        //{
        //    string sql = "";
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(1, "@icm_res_sub_grp_seq", icm_res_sub_grp_seq);

        //        sql = "select icm_res_grp_seq"
        //            + " from icm_mas_resource_sub_group"
        //            + " where icm_res_sub_grp_seq = @icm_res_sub_grp_seq";

        //        DataTable dt = new DataTable();
        //        dbManager.Open();
        //        dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

        //        if ()

        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        public DataTable ListResourceGrp(string icm_res_type_seq)
        {
            string sql = "";
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_res_type_seq", icm_res_type_seq);

                sql = "select sg.ResSubGrpId as Id"
                    + ", concat(sg.Code, '-', sg.Name) as Name"
                    + " from IcmMasResourceSubGroup sg"
                    + " left join IcmMasResourceGroup g on sg.ResGrpId = g.ResGrpId"
                    + " where sg.RecordStatus not like @status"
                    + (!string.IsNullOrEmpty(icm_res_type_seq) ? " and g.ResTypeId in (" + icm_res_type_seq + ")" : "")
                    //+ ((icm_res_grp_seq.Length > 0) ? " and icm_res_grp_seq = @icm_res_grp_seq" : "")
                    + " order by sg.Code, sg.Name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListOptionCombo(string Type)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@type", Type);

                string sql = " select bgm_option_seq as value, concat(bgm_option_code, ' - ', bgm_option_name) as text"
                             + " from bgm_mas_option"
                             + " where bgm_option_type_seq = @type"
                             + " and record_status = @status"
                             +
                             " and bgm_option_seq not in (select upper_bgm_option_seq from bgm_mas_option where upper_bgm_option_seq is not null and bgm_option_type_seq = @type and record_status = @status)"
                             + " order by bgm_option_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListResourceDDL(Nullable<int> bgm_exp_seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_seq", bgm_exp_seq);

                string sql = "select concat(cast(i.ResItemId as char), '-', cast(COALESCE(i.StdPrice, i.MaxPrice, '') as char)) as icm_res_item_seq "
                    + ", concat(coalesce(i.Code, ''), ' - ', coalesce(i.Name, '')) as icm_res_item_name"
                    + " from bgm_mas_expense e"
                    + " inner join IcmMasResourceItem i on e.icm_resource_seq = i.ResSubGrpId"
                    + " where i.RecordStatus not like @status"
                    + (bgm_exp_seq != null ? " and e.bgm_exp_seq = @bgm_exp_seq" : "")
                    + " order by i.Name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListOptionTree(string Type)
        {
            try
            {
                string sql = " select bgm_option_seq as value, bgm_option_name as text, upper_bgm_option_seq as parent"
                             + " from bgm_mas_option"
                             + " where bgm_option_type_seq = " + Type;
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadExpenseRate()
        {
            try
            {
                string sql = " select bgm_option_seq as value, bgm_option_name as text, upper_bgm_option_seq as parent"
                             + " from bgm_mas_option"
                             + " where bgm_option_type_seq = ";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListApprovalHeader(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                string sql = " select bgm_app_header_seq as value, bgm_app_header_name as text"
                             + " from bgm_approval_header"
                             + " where record_statusnot like@RecordStatus and bgm_year=@year"
                             + " order by bgm_app_header_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        /* brief ดึง EmployeeInfo จากของผู้ดำรงตำแหน่งราชการที่มีการเซตว่าเป็นหัวหน้าของสังกัดที่ค้นหา จากหมาเลขหน่วยงานที่ระบุ */
        public DataRow GetJobSiteHeadEmployee(string jobSiteSeq)
        {
            string errorMessage = null;
            return GetJobSiteHeadEmployee(jobSiteSeq, ref errorMessage);
        }

        public bool IsCurrentEmpSeqIsHead(string jobSiteSeq, string empSeq)
        {
            string errorMessage = "";
            DataRow headRow = GetJobSiteHeadEmployee(jobSiteSeq, ref errorMessage);
            if (headRow != null && headRow["emp_seq"] != null && Convert.ToString(headRow["emp_seq"]) == empSeq)
            {
                return true;
            }
            else return false;
        }
        /* brief ดึง EmployeeInfo จากของผู้ดำรงตำแหน่งราชการที่มีการเซตว่าเป็นหัวหน้าของสังกัดที่ค้นหา จากหมาเลขหน่วยงานที่ระบุ */
        public DataRow GetJobSiteHeadEmployee(string jobSiteSeq, ref string errorMessage)
        {
            DataRow result = null;
            try
			{
				dbManager.Open();
				dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@JobSiteSeq", jobSiteSeq);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);





                // Note : job site must be job site type 2. 
                const string strSql =
@"SELECT 
	j.job_site_seq, 
	j.job_site_name, 
	j.head_pos_seq,
	p.pos_name,
	e.pos_seq,
	e.emp_seq,
	e.first_name,
	e.last_name,
concat(e.first_name, '  ', e.last_name, ' (', p.pos_name , ')' ) as fullnamepos





FROM hrm_mp_job_sites j
    LEFT JOIN hrm_mp_positions p ON (j.head_pos_seq = p.pos_seq AND  p.record_status = @ActiveRecord)
    LEFT JOIN hrm_ps_employees e ON (e.pos_seq = p.pos_seq AND e.record_status = @ActiveRecord)

WHERE j.job_site_seq = @JobSiteSeq
    AND j.record_status = @ActiveRecord";






                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0];



                if (dtb.Rows.Count == 0)
                    errorMessage = "ไม่พบข้อมูลของหน่วยงานนี้";
			    else
                {
                    DataRow rowResult = dtb.Rows[0];
                    int? posSeq = rowResult["pos_seq"] as int?;
                    if (!posSeq.HasValue)
                    {
                        errorMessage = "หน่วยงานนี้ไม่มีการตั้งค่าให้ตำแหน่งใดๆ เป็นหัวหน้าสำนัก";
                    }
                    else
                    {
                        int? empSeq = rowResult["emp_seq"] as int?;
                        if (!empSeq.HasValue)
                        {
                            errorMessage = "ตำแหน่งที่เป็นหัวหน้าสำนักของที่นี่ ไม่มีคนครองตำแหน่ง";
                        }
                        else
                        {
                            errorMessage = "";
                            //result = rowResult;
                        }

                        result = rowResult;
                    }
                }
			}
			catch (Exception ex)
			{
			    errorMessage = ex.Message;
			}
			finally
			{
				dbManager.Dispose();
			}
            return result;
        }

        public class PositionWorkingJobsites
        {
            public string ID { get; set; }
            public string pos_ref { get; set; }
            public string pos_name { get; set; }
            public string exec_pos_name { get; set; }
            public string parent_ID { get; set; }
            public string emp_name { get; set; }
            public string job_site_seq { get; set; }
            public string job_site_name { get; set; }
            public string internal_job_site_seq { get; set; }
            public string internal_job_site_name { get; set; }
            public string emp_type_name { get; set; }

            public PositionWorkingJobsites()
            {
            }

            public List<PositionWorkingJobsites> GetArray(DataRowCollection dataRowCollection)
            {
                var listPositionWorkingJobsites = new List<PositionWorkingJobsites>();
                foreach (DataRow dataRow in dataRowCollection)
                {
                    listPositionWorkingJobsites.Add(
                        new PositionWorkingJobsites(
                            ConvertDatarow(dataRow, "ID"),
                            ConvertDatarow(dataRow, "pos_ref"),
                            ConvertDatarow(dataRow, "pos_name"),
                            ConvertDatarow(dataRow, "exec_pos_name"),
                            ConvertDatarow(dataRow, "parent_ID"),
                            ConvertDatarow(dataRow, "emp_name"),
                            ConvertDatarow(dataRow, "job_site_seq"),
                            ConvertDatarow(dataRow, "job_site_name"),
                            ConvertDatarow(dataRow, "internal_job_site_seq"),
                            ConvertDatarow(dataRow, "internal_job_site_name"),
                            ConvertDatarow(dataRow, "emp_type_name")
                            ));
                }
                return listPositionWorkingJobsites;
            }

            private string ConvertDatarow(DataRow dataRow, string key)
            {
                return Convert.IsDBNull(dataRow[key]) ? "" : (dataRow[key]).ToString();
            }

            public PositionWorkingJobsites(string ID, string pos_ref, string pos_name, string exec_pos_name,
                                           string parent_ID, string emp_name, string job_site_seq, 
                                           string job_site_name, string internal_job_site_seq, string internal_job_site_name,
                                           string emp_type_name)
            {
                this.ID = ID;
                this.pos_ref = pos_ref;
                this.pos_name = pos_name;
                this.exec_pos_name = exec_pos_name;
                this.parent_ID = parent_ID;
                this.emp_name = emp_name;
                this.job_site_seq = job_site_seq;
                this.job_site_name = job_site_name;
                this.internal_job_site_seq = internal_job_site_seq;
                this.internal_job_site_name = internal_job_site_name;
                this.emp_type_name = emp_type_name;
            }
        }

        public DataTable GetInfoFromRefSeq(string ref_seq, string ref_type)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@seq", ref_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@ref_type", ref_type);

                strSQL = "SELECT re.reply_seq, re.reply_date, re.ref_no, re.reply_text"
                    + ", re.from_emp_seq, re.to_emp_seq, re.from_send_seq"
                    + ", re.message_status, re.reply_type"
                    + ", concat(cast(np1.prefix_name as char), cast(user1.first_name as char), ' ', cast(user1.last_name as char)) as from_user_name"
                    + ", concat(cast(np2.prefix_name as char), cast(user2.first_name as char), ' ', cast(user2.last_name as char)) as to_user_name"
                    + ", pos1.pos_name as from_user_pos_name, pos2.pos_name as to_user_pos_name"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", re.remarks, re.record_status"
                    + ", re.created_by, re.created_when, re.updated_by, re.updated_when"
                    + ", send.from_pos_seq, send.to_pos_seq , send.wod_emp_seq "

                    + " FROM message_reply re"
                    + " LEFT JOIN message_send send ON re.from_send_seq = send.send_seq"
                    + " LEFT JOIN hrm_ps_employees user1 ON send.from_emp_seq = user1.emp_seq"
                    + " LEFT JOIN hrm_ps_employees user2 ON send.to_emp_seq = user2.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np1 ON user1.name_prefix_seq = np1.name_prefix_seq"
                    + " LEFT JOIN mas_name_prefixes np2 ON user2.name_prefix_seq = np2.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions pos1 ON user1.pos_seq = pos1.pos_seq"
                    + " LEFT JOIN hrm_mp_positions pos2 ON user2.pos_seq = pos2.pos_seq"
                    + " WHERE send.ref_seq = @seq"
                    + " AND send.ref_type = @ref_type"
                    + " AND send.record_status not like @delete_status"
                    + " AND re.record_status not like @delete_status"
                    + " order by re.reply_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }
    }
}
